package com.cevent.community.app.template.utils;/**
 * Created by Cevent on 2021/9/15.
 */

import com.cevent.community.app.template.generator.EnumsApplication;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author cevent
 * @description 数据库连接及table-column获取
 * static声明静态对象，可以在其他类直接调用class.method(param)
 * @date 2021/9/15 10:03
 */
public class DataBaseUtil {
    public static final Logger LOG = LoggerFactory.getLogger(DataBaseUtil.class);

    //1.获取数据库连接
    public static Connection getConnection() {
        Connection connection = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://192.168.139.130:3306/demo_test";
            String user = "root";
            String password = "cevent";
            connection = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return connection;
    }

    //2.获取表注释
    public static String getTableComment(String tableName) throws SQLException {
        Connection connection = getConnection();
        Statement statement = connection.createStatement();
        //查询注释，来自表的结构，根据传入的tableName查询
        ResultSet resultSet = statement.executeQuery(
                "SELECT table_comment from information_schema.TABLES where TABLE_NAME='" + tableName + "'"
        );
        String tableNameCN = "";
        if (resultSet != null) {
            while (resultSet.next()) {
                tableNameCN = resultSet.getString("table_comment");
                break;
            }
        }
        resultSet.close();
        statement.close();
        connection.close();
        LOG.info("DataBaseUtil-->ResultSet获取表名-------> " + tableNameCN);
        return tableNameCN;
    }

    //3.下划线转小驼峰
    public static String lineToSmallHump(String str) {
        //调用java.util的regular正则模式pattern
        Pattern linePattern = Pattern.compile("_(\\w)");
        //转小写
        str = str.toLowerCase();
        Matcher matcher = linePattern.matcher(str);
        StringBuffer stringBuffer = new StringBuffer();
        while (matcher.find()) {
            //匹配到下划线，指定索引index=1转为大写
            matcher.appendReplacement(stringBuffer, matcher.group(1).toUpperCase());
        }
        //拼接字符串尾部
        matcher.appendTail(stringBuffer);
        return stringBuffer.toString();
    }

    //4.下划线转大驼峰
    public static String lineToBigHump(String str) {
        //先转小驼峰，循环迭代
        String small = lineToSmallHump(str);
        return small.substring(0, 1).toUpperCase() + small.substring(1);
    }

    //5.数据库类型转为java类型
    public static String sqlTypeToJava(String sqlType) {
        if (sqlType.toUpperCase().contains("varchar".toUpperCase())
                || sqlType.toUpperCase().contains("char".toUpperCase())
                || sqlType.toUpperCase().contains("text".toUpperCase())) {
            return "String";
        } else if (sqlType.toUpperCase().contains("datetime".toUpperCase())) {
            return "Date";
        } else if (sqlType.toUpperCase().contains("int".toUpperCase())) {
            return "Integer";
        } else if (sqlType.toUpperCase().contains("long".toUpperCase())) {
            return "Long";
        } else if (sqlType.toUpperCase().contains("decimal".toUpperCase())) {
            return "BigDecimal";
        } else {
            //非以上类型，都转为string
            return "String";
        }
    }

    //6.数据库列类型转换
    public static List<FieldUtil> transferColumnByTableName(String tableName) throws SQLException{
        List<FieldUtil> fieldUtilList=new ArrayList<>();
        Connection connection=getConnection();
        Statement statement=connection.createStatement();
        //根据tableName执行sql，获取所有列
        ResultSet resultSet=statement.executeQuery("SHOW FULL COLUMNS FROM "+tableName+"");
        if(resultSet!=null){
            while (resultSet.next()){
                String columnName=resultSet.getString("Field");
                String type=resultSet.getString("Type");
                String comment=resultSet.getString("Comment");
                String nullAble=resultSet.getString("Null"); //yes / no

                FieldUtil fieldUtil=new FieldUtil();
                fieldUtil.setName(columnName);
                fieldUtil.setNameSmallHump(lineToSmallHump(columnName));
                fieldUtil.setNameBigHump(lineToBigHump(columnName));
                fieldUtil.setType(type);
                fieldUtil.setJavaType(DataBaseUtil.sqlTypeToJava(resultSet.getString("Type")));
                fieldUtil.setComment(comment);
                //注释携带|竖线，截取之前的内容
                if(comment.contains("|")){
                    fieldUtil.setNameCN(comment.substring(0,comment.indexOf("|")));
                }else{
                    fieldUtil.setNameCN(comment);
                }
                //为空校验，如果获取到的Null为yes，那么NullAble=true
                fieldUtil.setNullAble("YES".equals(nullAble));
                //长度校验
                if(type.toUpperCase().contains("varchar".toUpperCase())){
                    //如果length>0，需要对length校验
                    String len=type.substring(type.indexOf("(")+1,type.length()-1);
                    //存入varchar长度
                    fieldUtil.setLength(Integer.valueOf(len));
                }else{
                    //length=0则不校验
                    fieldUtil.setLength(0);
                }
                //**判断comment注释中有枚举，则提取EnumClass名
                if(comment.contains("枚举")){
                    fieldUtil.setEnums(true);
                    //comment中，以等级为例，从注释中的“枚举：[LevelEnum]”,得到LEVEL  [内部写入枚举名=js中的name]
                    int start=comment.indexOf("[");
                    int end=comment.indexOf("]");
                    //截取[]字符串
                    String enumsName=comment.substring(start+1,end);
                    String enumsConst= EnumsApplication.toUnderLine(enumsName);
                    fieldUtil.setEnumsConst(enumsConst);
                }else{
                    fieldUtil.setEnums(false);
                }
                fieldUtilList.add(fieldUtil);
            }
        }
        resultSet.close();
        statement.close();
        connection.close();
        LOG.info("DataBaseUtil返回转换的数据table-column：{}",fieldUtilList);
        return fieldUtilList;
    }
}
